package com.srit.forecast.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.srit.core.bean.Page;
import com.srit.forecast.bean.ReportConfig;
import com.srit.forecast.constant.Constant;
import com.srit.forecast.dao.ReportConfigDao;

@Service
@Transactional
public class ReportConfigDaoImpl implements ReportConfigDao {
	// TC_REPORTCONFIG,SEQ_TC_REPORTCONFIG ID,NAME
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public void save(ReportConfig reportConfig) {
		String sql = "insert into TC_REPORTCONFIG(id,name,enabled,deleted,orderno) values(SEQ_TC_REPORTCONFIG.nextval,?,?,?,?)";
		jdbcTemplate.update(sql, new Object[] { reportConfig.getName(),
				reportConfig.getEnabled(), reportConfig.getDeleted(),
				reportConfig.getOrderNo() });

	}

	@Override
	public void update(ReportConfig reportConfig) {
		String sql = "update  TC_REPORTCONFIG  set name=?,enabled=?,deleted=?,ORDERNO=? where id=?";
		jdbcTemplate.update(sql, new Object[] { reportConfig.getName(),
				reportConfig.getEnabled(), reportConfig.getDeleted(),
				reportConfig.getOrderNo(), reportConfig.getId() });

	}

	@Override
	public ReportConfig getById(Integer id) {
		String sql = "select * from TC_REPORTCONFIG where id=?";
		return jdbcTemplate.queryForObject(sql, new Object[] { id },
				new ReportInfoRowMapper());
	}

	@Override
	public void delete(Integer id) {
		String sql = "delete TC_REPORTCONFIG where id=?";
		jdbcTemplate.update(sql, new Object[] { id });
	}

	@Override
	public void page(Page page) {
		String sqlCount = "select count(*) from TC_REPORTCONFIG where 1=1 ";
		String sqlAnd = " ";
		String sqlSelect = "select * "
				+ "from (select rownum r,t1.* from TC_REPORTCONFIG t1 where rownum<? "
				+ sqlAnd + ") t2 where t2.r>?" + Constant.SQL_ORDER_BY_ID;
		int count = jdbcTemplate.queryForInt(sqlCount + sqlAnd);
		List<ReportConfig> list = jdbcTemplate.query(sqlSelect, new Object[] {
				(page.getPageNum() + 1) * page.getPageSize(),
				page.getPageNum() * page.getPageSize() },
				new ReportInfoRowMapper());
		page.setCount(count);
		page.setList(list);
	}

	@Override
	public int getMaxId() {
		String sql = "select max(id) from TC_REPORTCONFIG";
		return jdbcTemplate.queryForInt(sql);
	}

	@Override
	public List<ReportConfig> listEnabled() {
		String sql = "select * from TC_REPORTCONFIG where enabled='Y'";
		return jdbcTemplate.query(sql, new ReportInfoRowMapper());
	}

}

class ReportInfoResultSetExtractor implements ResultSetExtractor {

	@Override
	public Object extractData(ResultSet rs) throws SQLException,
			DataAccessException {
		ReportConfig reportConfig = new ReportConfig();
		reportConfig.setId(rs.getInt("ID"));
		reportConfig.setEnabled(rs.getString("ENABLED"));
		reportConfig.setDeleted(rs.getString("DELETED"));
		reportConfig.setOrderNo(rs.getString("ORDERNO"));
		reportConfig.setName(rs.getString("NAME"));
		return reportConfig;
	}

}

class ReportInfoRowMapper implements RowMapper {

	@Override
	public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
		ReportInfoResultSetExtractor extractor = new ReportInfoResultSetExtractor();
		return extractor.extractData(rs);
	}
}
